﻿CREATE PROCEDURE [dbo].[ListAllUsersWithProjectMembership]
@ProjectID INT
AS
SELECT u.UserID,
	u.UserName,
	u.RoleID,
	u.FullName,
	CASE WHEN m.ProjectID IS NULL THEN 0 ELSE 1 END IsMember,
	m.StartDate,
	m.EndDate,
	m.HourlyRate
FROM Users u 
	LEFT OUTER JOIN ProjectMembers m ON u.UserID = m.UserID AND m.ProjectID = @ProjectID
ORDER BY CASE WHEN m.ProjectID IS NULL THEN 0 ELSE 1 END DESC, u.UserName


